import pymysql
from datetime import datetime
import pandas as pd

class daily_update_assessment():
    def __init__(self,table_name):
        self.host = '192.168.1.145'
        self.port = 3306
        self.user = 'root'
        self.password = '280303'
        self.db = 'assessment'
        self.table = table_name
        self.charset = "utf8"


    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db,
                                    charset=self.charset)
        self.cursor = self.conn.cursor() 
        
    
    def closedb(self):
        """关闭数据库连接"""
        self.cursor.close()
        self.conn.close()


    def execute_and_disconnect(self):
        self.conn.commit()
        self.conn.close()


    def exec_sql(self, sql, value=()):
        cur = self.conn.cursor()
        cur.execute(sql, value)
        res = cur.fetchall()
        cur.close()
        return res
    
    
    def querydb(self, sql:str, data_format:str = "tuple"):
        """
        执行查询语句
        默认返回元组，若data_format = "dataframe"，则返回datafrme格式
        """
        try:
            self.connect()
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            if data_format == "dataframe":
                cols = self.cursor.description
                col = []
                for item in cols:
                    col.append(item[0])
                result = pd.DataFrame(result, columns = col)
        except Exception as ex:
            print(ex)
            raise
        self.closedb()
        return result

    def select_all(self):
        # self.connect()
        select_table_sql = "select * from " + self.table
        res_dataframe = self.querydb(sql = select_table_sql, data_format="dataframe")
# =============================================================================
#         table_names = self.exec_sql(select_table_sql)
#         table_name = table_names[0][0]
# 
#         select_all_sql = "select * from `" + table_name + "`"
#         data = self.exec_sql(select_all_sql)
# 
#         select_columns_sql = "select * from information_schema.columns where table_schema='" + self.db + "' and table_name='" + table_name + "'"
#         columns = self.exec_sql(select_columns_sql)
#         columns = [columns[i][3] for i in range(len(columns))]
# 
#         self.execute_and_disconnect()
#         res_dataframe = pd.DataFrame(data, columns=columns)
# 
# =============================================================================
        return res_dataframe

if __name__ == '__main__':
    Update = daily_update_assessment("basic_feature_score")
    res = Update.select_all()

